﻿using BCCommon;
using BCEntity.TMS.MarketingCoupon.MarketingCoupon;
using BCEntity.TMS.MarketingCoupon.MarketingCouponType;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.MarketingCoupon.MarketingCouponType
{
    public class MarketingCouponTypeData : IMarketingCouponTypeData
    {
        private const string SQL_QUERY = @" SELECT
		                                        A.MarketingCouponTypeId,
		                                        A.CouponType,
		                                        A.CouponName,
		                                        A.CouponSummark,
		                                        A.CouponAmount,
		                                        A.CouponCount,
		                                        A.RemainCount,
		                                        A.ReceivedCount,
		                                        A.UsingCount,
		                                        A.InvalidCount,
		                                        A.Enabled,
		                                        A.AgentRecordId,
		                                        A.IsCommon,
		                                        A.StartDate,
		                                        A.EndDate,
		                                        A.CreateAdminId,
		                                        A.CreateAdminName,
		                                        A.CreateTime,
                                                A.UpdateTime,
                                                A.EditAdminId,
                                                A.EditAdminName,
                                                A.IsCommonBusiness,
                                                A.VehicleTransportBusinessConfigId,
                                                A.TransportBusiness
                                            FROM
                                                marketingcoupontype A ";
        private readonly IDatabaseContext database;
        public MarketingCouponTypeData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<MarketingCouponTypeEntity> InsertAsync(MarketingCouponTypeEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<MarketingCouponCountEntity> GetAllCount(long? agentRecordId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (agentRecordId.HasValue)
            {
                wheres.Add("AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sqlCount = @" SELECT SUM(CouponCount) AS MarketingCouponAllCount,
                        SUM(RemainCount) AS MarketingCouponRemainCount,
                        SUM(ReceivedCount) AS MarketingCouponReceiveCount,
                        SUM(UsingCount) AS MarketingCouponUsingCount
                        ,SUM(InvalidCount) AS MarketingCouponInvalidCount
                         FROM marketingcoupontype ";
            var sql = $" {sqlCount} {whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponCountEntity>(sql, parameters);
        }

        public async Task<MarketingCouponTypeEntity> UpdateAsync(MarketingCouponTypeEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<MarketingCouponTypeEntity> GetSingle(long marketingCouponTypeId)
        {
            var sql = typeof(MarketingCouponTypeEntity).GetSelectSqlByPrimaryKeys();
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponTypeEntity>(sql, new { marketingCouponTypeId });
        }

        public async Task<IEnumerable<MarketingCouponTypeEntity>> GetList(long agentRecordId, bool? isEnabled,bool? isValid, decimal? couponMinAmount,
            decimal? couponMaxAmount)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int32);
            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            if (couponMinAmount.HasValue)
            {
                wheres.Add("A.CouponAmount >= ?CouponMinAmount");
                parameters.Add("?CouponMinAmount", couponMinAmount.Value, DbType.Decimal);
            }
            if (couponMaxAmount.HasValue)
            {
                wheres.Add("A.CouponAmount <= ?CouponMaxAmount");
                parameters.Add("?CouponMaxAmount", couponMaxAmount.Value, DbType.Decimal);
            }
            if (isValid.HasValue)
            {
                if (isValid.Value)
                {
                    wheres.Add("A.EndDate >= curdate()");
                }
                else
                {
                    wheres.Add("A.EndDate < curdate()");
                }
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $"{SQL_QUERY} {whereSql} GROUP BY A.MarketingCouponTypeId ORDER BY A.CreateTime DESC ";
            return await this.database.Slave.QueryAsync<MarketingCouponTypeEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<MarketingCouponTypeEntity>, int)> Query(bool? isEnabled, decimal? couponMinAmount, decimal? couponMaxAmount,
            int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            if (couponMinAmount.HasValue)
            {
                wheres.Add("A.CouponAmount >= ?CouponMinAmount");
                parameters.Add("?CouponMinAmount", couponMinAmount.Value, DbType.Decimal);
            }
            if (couponMaxAmount.HasValue)
            {
                wheres.Add("A.CouponAmount <= ?CouponMaxAmount");
                parameters.Add("?CouponMaxAmount", couponMaxAmount.Value, DbType.Decimal);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sqlCount = " SELECT COUNT(A.MarketingCouponTypeId) FROM marketingcoupontype A ";
            string sql = $@"{SQL_QUERY} {whereSql} GROUP BY A.MarketingCouponTypeId  ORDER BY A.CreateTime DESC 
                            LIMIT {(pageIndex - 1) * pageSize}, {pageSize}; {sqlCount} {whereSql} ";
            return await this.database.Slave.QueryPageableListAsync<MarketingCouponTypeEntity>(sql, parameters);
        }

        public async Task<IEnumerable<MarketingCouponTypeEntity>> GetCanReceiveList(long agentRecordId, string countyCode)
        {
            //现在的sql存在一种不可能的情况  iscommon字段代表是总部发放优惠券,但是目前应该没有这种情况,现不考虑
            var sql = $@" {SQL_QUERY}
                            INNER JOIN marketingcouponaddress B ON A.MarketingCouponTypeId=B.MarketingCouponTypeId
                            LEFT JOIN marketingcouponreceive C ON A.MarketingCouponTypeId=C.MarketingCouponTypeId
                        WHERE B.CountyCode=?CountyCode AND A.RemainCount>0 AND A.AgentRecordId=?AgentRecordId AND A.IsCommon=0 
                            AND A.Enabled=1 AND A.StartDate<=CURDATE() AND A.EndDate>CURDATE()
                            AND C.MarketingCouponTypeId IS NULL
                        GROUP BY A.MarketingCouponTypeId
                        UNION ALL
                        {SQL_QUERY}
                        LEFT JOIN marketingcouponreceive C ON A.MarketingCouponTypeId=C.MarketingCouponTypeId
                        WHERE A.IsCommon=1 AND A.RemainCount>0 AND A.Enabled=1 AND A.StartDate<=CURDATE() AND A.EndDate>CURDATE()
                            AND C.MarketingCouponTypeId IS NULL ; ";
            return await this.database.Slave.QueryAsync<MarketingCouponTypeEntity>(sql, new { agentRecordId, countyCode });
        }

        public async Task<MarketingCouponTypeEntity> Receive(long marketingCouponTypeId)
        {
            var sql = $@"UPDATE MarketingCouponType SET RemainCount=RemainCount-1,ReceivedCount=ReceivedCount+1 WHERE MarketingCouponTypeId=?MarketingCouponTypeId;
                            {SQL_QUERY} WHERE A.MarketingCouponTypeId=?MarketingCouponTypeId";

            return await this.database.Master.QueryFirstOrDefaultAsync<MarketingCouponTypeEntity>(sql, new { marketingCouponTypeId }, database.Transaction);
                 
        }
    }
}
